﻿using System;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;


public class MySqlHelper : DataBaseHelper
{
    public override void GetConnectString(string ip, string port, string name, string psw, string dataTable)
    {
        Connecting =
            string.Format(
                "Data Source={0};port={1};User Id={2};Password={3};pooling=true;CharSet=utf8;Allow Zero Datetime=true;Database={4}",
                ip, port, name, psw, dataTable);
    }

    private static MySqlConnection _mySqlConnection;

    private MySqlConnection MySqlConnection
    {
        get { return _mySqlConnection ?? (_mySqlConnection = new MySqlConnection(Connecting)); }
    }

    public override void OpenConnection()
    {
        if (MySqlConnection.State == ConnectionState.Closed)
            MySqlConnection.Open();
    }

    public override void CloseConnection()
    {
        if (MySqlConnection.State == ConnectionState.Open)
            MySqlConnection.Close();
    }

    public override void ExecuteSql(string sql)
    {
        MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);
        try
        {
            OpenConnection();

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            throw new Exception("获取数据集失败:" + ex.Message);
        }
        finally
        {
            CloseConnection();
        }
    }

    public override DataTable GetDataTable(string sql)
    {

        //PubLog.WriteProcessLog("SQL:" + sql);

        DataTable dataTable = new DataTable();
        try
        {
            OpenConnection();

            //PubLog.WriteProcessLog("打开数据库成功.");

            MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            adapter.Fill(dataTable);

            //PubLog.WriteProcessLog("获取数据集成功.");
        }
        catch (Exception ex)
        {
            //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
            throw new Exception("获取数据集失败:" + ex.Message);
        }
        finally
        {
            CloseConnection();
        }

        return dataTable;
    }


    public override DataSet GetDataSet(string sql)
    {
        //PubLog.WriteProcessLog("SQL:" + sql);

        DataSet ds = new DataSet();
        try
        {
            OpenConnection();

            //PubLog.WriteProcessLog("打开数据库成功.");

            MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

            //cmd.CommandTimeout =PubCommon.ConnectionTimeOut;

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            adapter.Fill(ds);

            //PubLog.WriteProcessLog("获取数据集成功.");
        }
        catch (Exception ex)
        {
            //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
            throw new Exception("获取数据集失败:" + ex.Message);
        }
        finally
        {
            CloseConnection();
        }

        return ds;
    }

    private MySqlDataReader GetDataReader(string sql)
    {
        //PubLog.WriteProcessLog("SQL:" + sql);

        try
        {
            OpenConnection();

            //PubLog.WriteProcessLog("打开数据库成功.");

            MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

            //cmd.CommandTimeout = PubCommon.ConnectionTimeOut;

            MySqlDataReader reader = cmd.ExecuteReader();

            if (reader != null)

            {
                //PubLog.WriteProcessLog("获取数据集成功.");
                return reader;
            }
        }
        catch (Exception ex)
        {
            //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
            throw new Exception("获取数据集失败:" + ex.Message);
        }

        return null;
    }

    public override bool ExecuteFiles(string fileName, string sql)
    {
        try
        {
            using (MySqlDataReader sqlDataReader = GetDataReader(sql))
            {
                if (sqlDataReader != null)
                {
                    using (
                        StreamWriter sw = new StreamWriter(fileName, true,
                            System.Text.Encoding.GetEncoding("UTF-8")))
                    {

                        sw.AutoFlush = true;

                        #region 列头

                        int k = sqlDataReader.FieldCount;
                        string title = string.Empty;

                        for (int i = 0; i < k; i++)
                        {
                            string name = sqlDataReader.GetName(i);
                            if (i == 0)
                                title = name;
                            else
                                title = title + "," + name;
                        }

                        sw.WriteLine(title);

                        #endregion

                        //PubLog.WriteProcessLog("开始时间：" + DateTime.Now.ToString());
                        //再执行行数据
                        while (sqlDataReader.Read())
                        {
                            //string line = sqlDataReader[j].ToString();

                            string line = string.Empty;

                            for (int i = 0; i < k; i++)
                            {
                                string value = sqlDataReader[i].ToString();
                                if (i == 0)
                                    line = value;
                                else
                                    line = line + "," + value;
                            }

                            sw.WriteLine(line);
                        }

                        //PubLog.WriteProcessLog("计算时间："+DateTime.Now.ToString());

                        sw.Flush();
                        sw.Close();

                        //PubLog.WriteProcessLog("写文件时间：" + DateTime.Now.ToString());

                        sqlDataReader.Close();

                        return true;
                    }
                }
            }
        }
        catch (Exception exception)
        {
            //PubLog.WriteProcessLog(exception.Message);
        }
        finally
        {
            CloseConnection();
        }

        return false;
    }

}

